<?php

/**
 * Classes para acesso aos dados da BD
 *
 * @author Renato
 */
abstract class DBAccess {

    protected $host = "localhost";
    protected $dbname = "matematrix_36";
    private $username;
    private $password;
    protected $connection = null;

    protected function __construct($username, $password) {
        $this->username = $username;
        $this->password = $password;
        $this->connection = new mysqli($this->host, $this->username, $this->password, $this->dbname);
        if ($this->connection->connect_errno) {
            echo "Erro de ligação:" . $this->connection->connect_errno;
            echo "Mensagem de erro:" . $this->connection->connect_error;
        }
    }

    public function __destruct() {
        $this->connection->close();
    }
    
    public function validate($username = "", $password = "") {
        if (strlen($username) == 0 || strlen($password) == 0) {
            return -1;
        }

        $query = "SELECT id FROM utilizadores_tbl WHERE utilizador = ? and password = ?";
        $stmt = $this->connection->prepare($query);
        if ($stmt) {
            $stmt->bind_param("ss", $username, $password);
            if ($stmt->execute()) {
                $stmt->bind_result($id);
                $stmt->fetch();
                $stmt->free_result();
                $stmt->close();
                return $id;
            }
        }
        return -1;
    }

    public function isAdmin($id) {
        $id = intval($id);
        $result = $this->connection->query("SELECT id FROM administradores_tbl WHERE id = $id");
        $num = $result->num_rows;
        $result->free_result();
        return $num > 0;
    }

    public function getName($id) {
        $id = intval($id);
        $tipoQ = $this->connection->query("SELECT tipo FROM utilizadores_tbl WHERE id = $id");

        if ($tipoQ) {
            if ($tipoQ->num_rows > 0) {
                $row = $tipoQ->fetch_row();
                $tipo = $row[0];

                if ($tipo == 1) {
                    $query = "SELECT equipa FROM equipas_tbl WHERE id = $id";
                } elseif ($tipo == 2) {
                    $query = "SELECT nome FROM administradores_tbl WHERE id = $id";
                } else {
                    return null;
                }

                $nomeQ = $this->connection->query($query);
                $row = $nomeQ->fetch_row();
                $nomeQ->free_result();
                return $row[0];
            }
        }
        return null;
    }

    public function getPontuacaoTotalMes($ordemGeografica = false) {
        $order = $ordemGeografica ? " ORDER BY localidade" : "";
        return $this->connection->query("SELECT * FROM pontuacao_total_mes" . $order);
    }

    public function getPontuacaoTotalEquipas($ordemGeografica = false) {
        $order = $ordemGeografica ? " ORDER BY localidade" : "";
        return $this->connection->query("SELECT * FROM pontuacao_total_equipas" . $order);
    }

    public function getTop3Equipas($ordemGeografica = false) {
        $order = $ordemGeografica ? " ORDER BY localidade" : "";
        return $this->connection->query("SELECT * FROM top3_equipas" . $order);
    }

    public function getTop3Mes($ordemGeografica = false) {
        $order = $ordemGeografica ? " ORDER BY localidade" : "";
        return $this->connection->query("SELECT * FROM top3_equipas_mes" . $order);
    }

    public function getTop3Localidade($localidade, $mes = false) {
        $localidade = $this->connection->real_escape_string($localidade);
        $tabela = $mes ? "pontuacao_total_mes" : "pontuacao_total_equipas";
        $query = "SELECT id_equipa, equipa, localidade, pontuacao FROM $tabela " .
                "WHERE pontuacao >= (SELECT MIN(valor) FROM " .
                "(SELECT DISTINCT SUM(pontuacao) as valor FROM $tabela " .
                "WHERE localidade = '$localidade' GROUP BY id_equipa " .
                "ORDER BY valor DESC LIMIT 3) t3) " .
                "AND localidade = '$localidade' ORDER BY pontuacao DESC;";

        return $this->connection->query($query);
    }

    public function getEquipasLocalidade($localidade, $mes = false) {
        $localidade = $this->connection->real_escape_string($localidade);
        $tabela = $mes ? "pontuacao_total_mes" : "pontuacao_total_equipas";
        $query = "SELECT id_equipa, equipa, localidade, pontuacao FROM $tabela " .
                "WHERE localidade = '$localidade' ORDER BY pontuacao DESC;";

        return $this->connection->query($query);
    }

    public function getLocalidades() {
        return $this->connection->query("SELECT DISTINCT localidade FROM equipas_tbl ORDER BY localidade");
    }

    public function getEquipasOrdemRegisto($top3 = false) {
        $limit = $top3 ? " LIMIT 3" : "";
        return $this->connection->query("SELECT id as id_equipa, equipa, localidade, data_inscricao FROM equipas_tbl ORDER BY data_inscricao DESC" . $limit);
    }

    public function getDesempenhoEquipa($idEquipa = 0) {
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT * FROM desempenho_equipas WHERE id_equipa = $idEquipa");
    }

    public function getEpocaAtual() {
        return $this->connection->query("SELECT * FROM epocas_tbl WHERE curdate() BETWEEN data_ini AND data_fim");
    }

    public function getListaJornadas() {
        return $this->connection->query("SELECT j.id AS id_jornada, p.titulo, data_ini, data_fim " .
                        "FROM problemas_tbl p JOIN jornadas_tbl j ON p.id = j.id_problema " .
                        "ORDER BY data_ini DESC");
    }

    public function getJornada($idJornada) {
        $idJornada = intval($idJornada);
        return $this->connection->query("SELECT titulo, problema, sugestoes, resposta, data_ini, data_fim " .
                        "FROM problemas_tbl p JOIN jornadas_tbl j ON p.id = j.id_problema " .
                        "WHERE j.id = $idJornada");
    }

    public function getJornadaAtual() {
        return $this->connection->query("SELECT titulo, problema, sugestoes, resposta, data_ini, data_fim " .
                        "FROM problemas_tbl p JOIN jornadas_tbl j ON p.id = j.id_problema " .
                        "WHERE curdate() BETWEEN data_ini AND data_fim");
    }

    public function inserirEquipa($login, $nomeEquipa, $passwd, $email = '', $escola = '', $morada = '', $localidade = '', $codpostal = '', $professor = '', $telefone = 0, $elementos = '') {

        $insert = $this->connection->prepare("INSERT INTO utilizadores_tbl(utilizador,password,tipo) VALUES(?,?,1)");
        $insert->bind_param("ss", $login, $passwd);

        if (!$insert->execute()) {
            return false;
        }
        $idQ = $this->connection->prepare("SELECT id FROM utilizadores_tbl WHERE utilizador = ?");
        $idQ->bind_param("s", $login);
        $id = 0;
        if ($idQ->execute()) {
            $idQ->bind_result($id);
            $idQ->fetch();
            $idQ->free_result();
            $idQ->close();
            $insert2 = $this->connection->prepare(
                    "INSERT INTO equipas_tbl(id,equipa,email,escola,morada,localidade,codpostal,professor,telefone,elementos) 
                        VALUES(?,?,?,?,?,?,?,?,?,?)");
            if ($insert2) {
                $insert2->bind_param("isssssssis", $id, $nomeEquipa, $email, $escola, $morada, $localidade, $codpostal, $professor, $telefone, $elementos);
                $result = $insert2->execute();
                $insert2->free_result();
                $insert2->close();
                return $result ? $id : false;
            }
        }
        return false;
    }

    public function getDadosPublicosEquipa($idEquipa) {
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT equipa,escola,codpostal,localidade," .
                        "professor,elementos,data_inscricao,COALESCE(avatar_url,'http://localhost/Matematrix/images/avatares/avatar.png') AS avatar_url " .
                        "FROM equipas_tbl WHERE id = $idEquipa");
    }

    public function getAnuncioCorrente() {
        return $this->connection->query("SELECT assunto, texto FROM anuncios_tbl WHERE curdate() BETWEEN data_ini AND COALESCE(data_fim, curdate())");
    }
    
    public function getTopicosPublicos(){
        return $this->connection->query(
                "SELECT t.id, t.data_envio, t.assunto, e.equipa ".
                "FROM topicos_tbl t JOIN equipas_tbl e ON t.id_equipa = e.id ".
                "LEFT JOIN administradores_tbl a ON t.id_autor_resposta = a.id ".
                "WHERE t.privado = 0");
    }
    
    public function getTopico($idTopico) {
        $idTopico = intval($idTopico);
        return $this->connection->query(
                "SELECT t.*, e.equipa, a.nome as autor_resposta ".
                "FROM topicos_tbl t JOIN equipas_tbl e ON t.id_equipa = e.id ".
                "LEFT JOIN administradores_tbl a ON t.id_autor_resposta = a.id ".
                "WHERE t.id = $idTopico AND t.privado = 0");
    }

}

class DBUnauthConnection extends DBAccess {

    private $user = "geral";
    private $pass = "geral";

    public function __construct() {
        parent::__construct($this->user, $this->pass);
    }

}

abstract class DBAuthUserConnection extends DBAccess {

    public function __construct($username, $password) {
        parent::__construct($username, $password);
    }

    public function alterarFichaEquipa($id, $email = '', $escola = '', $morada = '', $localidade = '', $codpostal = '', $professor = '', $telefone = 0, $elementos = '', $ativo = true) {
        $updt = $this->connection->prepare("UPDATE equipas_tbl SET " .
                "email=?,escola=?,morada=?,localidade=?,codpostal=?," .
                "professor=?,telefone=?,elementos=?,ativo=? " .
                "WHERE id=?");

        $updt->bind_param("ssssssisii", $email, $escola, $morada, $localidade, $codpostal, $professor, $telefone, $elementos, $ativo, $id);

        $res = $updt->execute();
        $updt->free_result();
        $updt->close();
        return $res;
    }
    
    public function alterarAvatar($id, $avatarUrl){
        $updt = $this->connection->prepare("UPDATE equipas_tbl SET " .
                "avatar_url=? WHERE id=?");

        $updt->bind_param("si", $avatarUrl, $id);

        $res = $updt->execute();
        $updt->free_result();
        $updt->close();
        return $res;
    }

    public function alterarPassword($id, $oldPasswd, $newPasswd) {
        $res = $this->connection->query("UPDATE utilizadores_tbl SET password = '$newPasswd' WHERE id = $id AND password = '$oldPasswd'");
        if ($res) {
            return $this->connection->affected_rows > 0;
        }
        return false;
    }

    public function getTodosDadosEquipa($idEquipa) {
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT * FROM equipas_tbl WHERE id = $idEquipa");
    }

}

class DBTeamConnection extends DBAuthUserConnection {

    private $user = "equipa";
    private $pass = "equipa";

    public function __construct() {
        parent::__construct($this->user, $this->pass);
    }

    public function inserirResposta($idJornada, $idEquipa, $resposta) {
        $ins = $this->connection->prepare("INSERT INTO respostas_tbl(id_jornada,id_equipa,resposta) " .
                " VALUES(?,?,?)");
        if ($ins) {
            $ins->bind_param("iis", $idJornada, $idEquipa, $resposta);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function getRespostaEquipa($idJornada, $idEquipa) {
        $idJornada = intval($idJornada);
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT * FROM respostas_tbl WHERE id_jornada = $idJornada AND id_equipa = $idEquipa");
    }

    public function editarResposta($idJornada, $idEquipa, $resposta) {
        $ins = $this->connection->prepare("UPDATE respostas_tbl SET resposta = ? " .
                "WHERE id_jornada = ? AND id_equipa = ?");
        $ins->bind_param("sii", $resposta, $idJornada, $idEquipa);
        $res = $ins->execute();
        $ins->close();
        return $res;
    }

    public function getTodosTopicosEquipa($idEquipa) {
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT * FROM topicos_tbl WHERE id_equipa = $idEquipa");
    }

    public function getTopicosPrivadosEquipa($idEquipa) {
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT t.*, e.equipa, a.nome as autor_resposta ".
                "FROM topicos_tbl t JOIN equipas_tbl e ON t.id_equipa = e.id ".
                "LEFT JOIN administradores_tbl a ON t.id_autor_resposta = a.id ".
                "WHERE t.id_equipa = $idEquipa AND t.privado = 1");
    }

    public function getTopico($idTopico, $idEquipa) {
        $idTopico = intval($idTopico);
        $idEquipa = intval($idEquipa);
        return $this->connection->query("SELECT * FROM topicos_tbl WHERE id = $idTopico AND id_equipa = $idEquipa");
    }

    public function criarTopico($idEquipa, $assunto, $texto) {
        $ins = $this->connection->prepare("INSERT INTO topicos_tbl(id_equipa,assunto,texto) VALUES(?,?,?)");
        $ins->bind_param("iss", $idEquipa, $assunto, $texto);
        $res = $ins->execute();
        $ins->close();
        return $res;
    }

}

class DBAdminConnection extends DBAuthUserConnection {

    private $user = "admin";
    private $pass = "admin";

    public function __construct() {
        parent::__construct($this->user, $this->pass);
    }

    public function removerEquipa($idEquipa) {
        $idEquipa = intval($idEquipa);
        return $this->connection->query("UPDATE equipas_tbl SET ativo=false WHERE id = $idEquipa");
    }

    public function getRespostasJornada($idJornada) {
        $idJornada = intval($idJornada);
        return $this->connection->query("SELECT * FROM respostas_tbl WHERE id_jornada = $idJornada");
    }

    public function getRespostasPorCorrigir() {
        return $this->connection->query("SELECT e.equipa, r.id, p.titulo as problema, r.data_criacao " .
                        "FROM equipas_tbl e JOIN respostas_tbl r ON e.id = r.id_equipa " .
                        "JOIN jornadas_tbl j ON r.id_jornada = j.id JOIN problemas_tbl p ON j.id_problema = p.id " .
                        "WHERE corrigido = 0");
    }

    public function getResposta($idResposta) {
        $idResposta = intval($idResposta);
        return $this->connection->query("SELECT * FROM respostas_tbl WHERE id = $idResposta");
    }

    public function corrigirResposta($idResposta, $idCorretor, $pontuacao) {
        $pontuacao = intval($pontuacao);
        return $this->connection->query("UPDATE respostas_tbl SET pontuacao=$pontuacao, corrigido=true, id_corretor=$idCorretor " .
                        "WHERE id = $idResposta");
    }

    public function criarProblema($idAutor, $titulo, $problema, $resposta, $sugestoes) {

        $ins = $this->connection->prepare("INSERT INTO problemas_tbl(id_autor,titulo,problema,resposta,sugestoes) " .
                "VALUES(?,?,?,?,?)");
        if ($ins) {
            $ins->bind_param("issss", $idAutor, $titulo, $problema, $resposta, $sugestoes);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function getProblema($idProblema) {
        $idProblema = intval($idProblema);
        return $this->connection->query("SELECT * FROM problemas_tbl WHERE id = $idProblema");
    }
    
    public function getProblemasDisponiveis() {
        return $this->connection->query("SELECT id, titulo FROM problemas_tbl ".
                "WHERE publicado = 0 OR id NOT IN ".
                "(SELECT id_problema FROM jornadas_tbl WHERE id_epoca != ".
                "(SELECT id FROM epocas_tbl WHERE curdate() > data_fim))");
    }

    public function getProblemas() {
        return $this->connection->query("SELECT * FROM problemas_tbl");
    }

    public function getTopicos() {
        return $this->connection->query("SELECT t.*, e.equipa, a.nome as autor_resposta ".
                "FROM topicos_tbl t JOIN equipas_tbl e ON t.id_equipa = e.id ".
                "JOIN administradores_tbl a ON t.id_autor_resposta = a.id");
    }

    public function getTopico($idTopico) {
        $idTopico = intval($idTopico);
        return $this->connection->query("SELECT t.*, e.equipa, a.nome as autor_resposta FROM topicos_tbl t JOIN equipas_tbl e ON t.id_equipa = e.id JOIN administradores_tbl a ON t.id_autor_resposta = a.id WHERE t.id = $idTopico");
    }

    public function getMensagens() {
        return $this->connection->query("SELECT t.id, e.equipa, t.assunto, t.data_envio " .
                        "FROM topicos_tbl t JOIN equipas_tbl e ON t.id_equipa = e.id WHERE id_autor_resposta IS NULL");
    }

    public function editarProblema($idProblema, $titulo, $problema, $resposta, $sugestoes) {
        $ins = $this->connection->prepare("UPDATE problemas_tbl SET titulo=?,problema=?,resposta=?,sugestoes=? " .
                "WHERE id = ?");
        if ($ins) {
            $ins->bind_param("ssssi", $titulo, $problema, $resposta, $sugestoes, $idProblema);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function eliminarProblema($idProblema) {
        $idProblema = intval($idProblema);
        return $this->connection->query("DELETE FROM problemas_tbl WHERE id = $idProblema");
    }

    public function criarEpoca($idAutor, $dataInicio, $dataFim, $nomeEpoca, $regulamento) {
        $ins = $this->connection->prepare("INSERT INTO epocas_tbl(id_autor,data_ini,data_fim,nome,regulamento) " .
                "VALUES(?,?,?,?,?)");
        if ($ins) {
            $ins->bind_param("issss", $idAutor, $dataInicio, $dataFim, $nomeEpoca, $regulamento);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function getEpoca($idEpoca) {
        $idEpoca = intval($idEpoca);
        return $this->connection->query("SELECT e.*, ad.nome AS nome_autor FROM epocas_tbl e " .
                        "JOIN administradores_tbl ad ON ad.id = e.id_autor WHERE id = $idEpoca");
    }

    public function editarEpoca($idEpoca, $dataInicio, $dataFim, $nomeEpoca) {
        $ins = $this->connection->prepare("UPDATE epocas_tbl SET data_ini=?,data_fim=?,nome=? " .
                "WHERE id=?");
        if ($ins) {
            $ins->bind_param("sssi", $dataInicio, $dataFim, $nomeEpoca, $idEpoca);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function adicionarJornada($idAutor, $idProblema, $dataInicio, $dataFim) {
        $ins = $this->connection->prepare("INSERT INTO jornadas_tbl(id_autor,id_problema,data_ini,data_fim) " .
                "VALUES(?,?,?,?)");
        if ($ins) {
            $ins->bind_param("iiss", $idAutor, $idProblema, $dataInicio, $dataFim);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function criarAnuncio($idAutor, $idEpoca, $assunto, $texto, $dataInicio, $dataFim) {
        $ins = $this->connection->prepare("INSERT INTO anuncios_tbl(id_autor,id_epoca,assunto,texto,data_ini,data_fim) " .
                "VALUES(?,?,?,?,?,?)");
        if ($ins) {
            $ins->bind_param("iissss", $idAutor, $idEpoca, $assunto, $texto, $dataInicio, $dataFim);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function getAnuncio($idAnuncio) {
        $idAnuncio = intval($idAnuncio);
        return $this->connection->query("SELECT a.*, e.nome AS nome_epoca, ad.nome AS nome_autor " .
                        "FROM anuncios_tbl a LEFT JOIN epocas_tbl e ON a.id_epoca = e.id " .
                        "JOIN administradores_tbl ad ON ad.id = a.id_autor WHERE a.id = $idAnuncio");
    }

    public function getAnuncios() {
        return $this->connection->query("SELECT * FROM anuncios_tbl");
    }

    public function editarAnuncio($idAnuncio, $idEpoca, $assunto, $texto, $dataInicio, $dataFim) {
        $ins = $this->connection->prepare("UPDATE anuncios_tbl SET id_epoca=?,assunto=?,texto=?,data_ini=?,data_fim=COALESCE(?,curdate()) " .
                "WHERE id=?");
        if ($ins) {
            $ins->bind_param("issssi", $idEpoca, $assunto, $texto, $dataInicio, $dataFim, $idAnuncio);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function editarAnuncioPorOmissao($assunto, $texto) {
        $ins = $this->connection->prepare("UPDATE anuncios_tbl SET assunto=?,texto=? WHERE data_fim IS NULL");
        if ($ins) {
            $ins->bind_param("ss", $assunto, $texto);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function editarRegulamento($regulamento) {
        $regulamento = $this->connection->real_escape_string($regulamento);
        return $this->connection->query("UPDATE epocas_tbl SET regulamento = '$regulamento' WHERE data_ini = (SELECT MAX(data_ini) FROM epocas_tbl)");
    }

    public function responderATopico($idTopico, $idAdmin, $resposta) {
        $ins = $this->connection->prepare("UPDATE topicos_tbl SET id_autor_resposta=?,resposta=? WHERE id=?");
        if ($ins) {
            $ins->bind_param("isi", $idAdmin, $resposta, $idTopico);
            $res = $ins->execute();
            $ins->close();
            return $res;
        }
        return false;
    }

    public function tornarTopicoPublico($idTopico) {
        $idTopico = intval($idTopico);
        return $this->connection->query("UPDATE topicos_tbl SET privado=false WHERE id = $idTopico");
    }

    public function exportarHistorico($dir) {
        $query = "SELECT * INTO OUTFILE 'historico3.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\\n' FROM epocas_tbl ep LEFT JOIN jornadas_tbl j ON ep.id =j.id_epoca LEFT JOIN problemas_tbl p ON p.id = j.id_problema LEFT JOIN respostas_tbl r ON r.id_jornada = j.id LEFT JOIN equipas_tbl eq ON eq.id = r.id_equipa LEFT JOIN topicos_tbl t ON ep.id = t.id_epoca LEFT JOIN anuncios_tbl a ON a.id_epoca = ep.id WHERE ep.data_fim < curdate()";

        //$query = $this->connection->real_escape_string($query);

        return $this->connection->query($query);
    }

}

?>
